package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Degree;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

//储存数据
@Slf4j public final class DegreeDao {
    //新建degreeDao对象
    private static DegreeDao degreeDao = new DegreeDao();
    //唯一构造器
    public DegreeDao() {
    }
    //获得degreeDao实例
    public static DegreeDao getInstance() {
        return degreeDao;
    }



    /**
     * 增加学位信息
     * @param connection
     * @param degree
     * @return
     * @throws SQLException
     */
    public boolean add(Connection connection, Degree degree) throws SQLException {
        //预编译sql语句
        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO Degree (description,no,remarks)values (?,?,?)");
        //为参数设置值
        preparedStatement.setString(1,degree.getDescription());
        preparedStatement.setString(2,degree.getNo());
        preparedStatement.setString(3,degree.getRemarks());
        //执行并获得受影响的行数
        int rowAffected = preparedStatement.executeUpdate();
        //关闭
        JdbcHelper.close(null,preparedStatement);
        //返回增加是否成功
        return rowAffected>0;
    }

    /**
     * 根据id删除学位信息
     * @param connection
     * @param id
     * @return
     * @throws SQLException
     */
    public boolean delete(Connection connection,Integer id) throws SQLException {
        //预编译sql语句
        PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM Degree WHERE id=?");
        //删除响应id元组
        preparedStatement.setInt(1,id);
        //执行并获得受影响行数的值
        int rowAffected = preparedStatement.executeUpdate();
        //关闭
        JdbcHelper.close(null,preparedStatement);
        //返回是否删除成功
        return  rowAffected>0;
    }
    /**
     * 更新学位信息
     * @param connection
     * @param degree
     * @return
     * @throws SQLException
     */
    public boolean update(Connection connection, Degree degree) throws SQLException {
        //预编译sql语句
        PreparedStatement preparedStatement = connection.prepareStatement("UPDATE Degree SET description=?,no=?,remarks=? WHERE id=?");
        //为参数设值
        preparedStatement.setString(1,degree.getDescription());
        preparedStatement.setString(2,degree.getNo());
        preparedStatement.setString(3,degree.getRemarks());
        preparedStatement.setInt(4,degree.getId());
        //执行并获得受影响的行数
        int rowAffected = preparedStatement.executeUpdate();
        //关闭
        JdbcHelper.close(null,preparedStatement);
        //返回是否修改成功
        return rowAffected>0;
    }
    /**
     * 按照条件和分页查找结果集
     * @param connection
     * @param condition
     * @param pagination
     * @return
     * @throws SQLException
     */
    public Collection<Degree> findAll(Connection connection ,Collection<Condition> condition, Pagination pagination) throws SQLException {
        Collection<Degree> degrees = new TreeSet<>();
        //构建查找语句
        StringBuilder select_sql = new StringBuilder("SELECT * FROM Degree");
        if (condition != null){
            //构建条件查询where子句 select * from degree
            select_sql.append(Condition.toWhereClause(JSON.toJSONString(condition)));
            //select * from degree where jey op value and key op value...
            log.trace(select_sql.toString());
        }
        if (pagination != null){
            //获得元组总数
            int totalNum = CommonDao.getInstance().count(connection,"Degree");
            //构建分页查询limit子句
            select_sql.append(pagination.toLimitClause(totalNum));
            //select * from degree where jey op value and key op value... limit 3,5;
            log.trace(select_sql.toString());
        }
        PreparedStatement preparedStatement = connection.prepareStatement(select_sql.toString());
        //执行并获得查找到的结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        //获得数据
        while (resultSet.next()){
            int degree_id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            degrees.add(new Degree(degree_id,description,no,remarks));
        }
        //关闭
        JdbcHelper.close(resultSet,preparedStatement);
        //返回查找到的数据集
        return degrees;
    }

    /**
     * 根据id查找
     * @param connection
     * @param id
     * @return
     * @throws SQLException
     */
    public Degree find(Connection connection ,Integer id) throws SQLException {
        Degree foundDegree = null;
        //预编译sql语句
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM Degree WHERE id=?");
        //为参数设置值
        preparedStatement.setInt(1,id);
        //执行并获得查找到的结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        //获得数据
        if (resultSet.next()){
            int degree_id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            foundDegree = new Degree(degree_id,description,no,remarks);
        }
        //关闭
        JdbcHelper.close(resultSet,preparedStatement);
        //返回查找到的对象
        return foundDegree;
    }



}

